package authority.dao;

import java.sql.Connection;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import authority.comm.Lay;
import authority.entity.User;
import authority.util.DbBuilder;
/**
 * 用户操作数据库
 * @author shichenyang
 *
 */
public class UserDao {
	/**
	 * 验证登录
	 * @param user 返回结果
	 * @param connection 开启事务
	 * @return
	 */
	public User loginUser(User user,Connection connection){
		String sql ="select *,dept_id deptId,role roleId from user where name=? and password=?";
		user= DbBuilder.getBean(connection,sql, User.class,user.getName(),user.getPassword());
		return user;
	}
	/**
	 * 删除用户
	 * @param id
	 * @return
	 */
	public int delUser(String id){
		String sql ="update user set status='0' where id=?";
		return DbBuilder.delete(sql, id);
	}
	/**
	 * 加载用户的权限
	 * @param user
	 * @param conn
	 * @return
	 */
	public User loadPermission(User user,Connection conn){
		StringBuffer sql =new StringBuffer();
		sql.append(" SELECT\n");
		sql.append(" 	p.url ,\n");
		sql.append(" 	p.pid\n");
		sql.append(" FROM\n");
		sql.append(" 	user_role u,\n");
		sql.append(" 	role_permission r,\n");
		sql.append(" 	permission p\n");
		sql.append(" WHERE\n");
		sql.append(" 	u.rid = r.rid\n");
		sql.append(" AND r.pid = p.id\n");
		sql.append(" and u.uid=?");
		List<Map<String, Object>> list= DbBuilder.getListMap(conn, sql.toString(), user.getId());
		List<String> l=new ArrayList<String>();
		List<String> p=new ArrayList<String>();
		for (Map<String, Object> map : list) {
			l.add(map.get("url").toString());
			p.add(map.get("pid").toString());
		}
		user.setPermissions(l);
		user.setPid(p);
		return user;
	}

	/**
	 *
	 * @param user
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<User> findUser(User user,String startDate,String endDate){
		List<Object> l=new ArrayList<Object>();
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
		StringBuffer sql =new StringBuffer();
		sql.append(" SELECT\n");
		sql.append(" 	id,\n");
		sql.append(" 	`name`,\n");
		sql.append(" 	real_name realName,\n");
		sql.append(" 	`password`,\n");
		sql.append(" 	birthday,\n");
		sql.append(" role roleId,");
		sql.append(" d.name deptName,");
		sql.append(" 	create_time createTime,\n");
		sql.append(" 	update_time updateTime,\n");
		sql.append(" 	oper,\n");
		sql.append(" 	`status`\n");
		sql.append(" FROM\n");
		sql.append(" 	`user` ,dept d\n");
		sql.append(" WHERE\n");
		sql.append("  	1 = 1 and user.dept_id=d.id\n");
		if(user.getName()!=null&&!user.getName().isEmpty()){
			sql.append(" AND NAME LIKE CONCAT('%', ?, '%')\n");
			l.add(user.getName());
		}
		addParams(startDate, endDate, l, sdf, sql,user);
		sql.append(" and status='1'");
		sql.append(" order by update_time desc");
		return DbBuilder.getListBean(sql.toString(),User.class, l.toArray());
	}

	/**
	 * 添加条件
	 * @param startDate
	 * @param endDate
	 * @param l
	 * @param sdf
	 * @param sql
	 */
	private void addParams(String startDate, String endDate, List<Object> l, SimpleDateFormat sdf, StringBuffer sql,User user) {
		if(startDate!=null&&!startDate.isEmpty()){
			sql.append(" AND u.update_time>?\n");
			try {
				Date date=sdf.parse(startDate);
				l.add(date);
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}
		if(endDate!=null&&!endDate.isEmpty()){
			sql.append(" AND u.update_time<?\n");
			try {
				Date date=sdf.parse(endDate);
				l.add(date);
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}

		if("员工".equals(user.getRoleId())){
			sql.append(" AND u.id=?\n");
			l.add(user.getId());
		}
		if ("部门经理".equals(user.getRoleId())){
			sql.append(" and u.dept_id=?");
			l.add(user.getDeptId());
		}
	}

	/**
	 * 分页用
	 * @param user
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<User> findUserPage(User user,String startDate,String endDate,Lay lay){
		List<Object> l=new ArrayList<Object>();
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
		StringBuffer sql =new StringBuffer();
		sql.append(" SELECT\n");
		sql.append(" 	u.id,\n");
		sql.append(" 	u.`name`,\n");
		sql.append(" 	u.real_name realName,\n");
		sql.append(" 	u.`password`,\n");
		sql.append(" 	u.birthday,\n");
		sql.append(" 	u.role roleId,\n");
		sql.append(" 	d.`name` deptName,\n");
		sql.append(" 	u.create_time createTime,\n");
		sql.append(" 	u.update_time updateTime,\n");
		sql.append(" 	u.oper,\n");
		sql.append(" 	u.`status`\n");
		sql.append(" FROM\n");
		sql.append(" 	`user` u,\n");
		sql.append(" 	dept d\n");
		sql.append(" WHERE\n");
		sql.append(" 	1 = 1\n");
		sql.append(" AND u.dept_id = d.id\n");

		if(user.getName()!=null&&!user.getName().isEmpty()){
			sql.append(" AND u.NAME LIKE CONCAT('%', ?, '%')\n");
			l.add(user.getName());
		}
		addParams(startDate, endDate, l, sdf, sql,user);
		sql.append(" and u.status >0");
		sql.append(" order by u.update_time desc");
		sql.append(" limit ?,?");
		l.add((lay.getPage()-1)*lay.getLimit());
		l.add(lay.getLimit());
		return DbBuilder.getListBean(sql.toString(), User.class,l.toArray());
	}
	/**
	 * 查询所有有效用户个数
	 * @param user
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public Integer countUser(User user,String startDate,String endDate){
		List<Object> l=new ArrayList<Object>();
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
		StringBuffer sql =new StringBuffer();
		sql.append(" SELECT\n");
		sql.append(" 	count(*)\n");
		sql.append(" FROM\n");
		sql.append(" 	`user`\n");
		sql.append(" WHERE\n");
		sql.append(" 	1 = 1\n");
		if(user.getName()!=null&&!user.getName().isEmpty()){
			sql.append(" AND NAME LIKE CONCAT('%', ?, '%')\n");
			l.add(user.getName());
		}
		if(startDate!=null&&!startDate.isEmpty()){
			sql.append(" AND update_time > ?\n");
			try {
				Date date=sdf.parse(startDate);
				l.add(date);
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}
		if(startDate!=null&&!startDate.isEmpty()){
			sql.append(" AND update_time < ?\n");
			try {
				Date date=sdf.parse(startDate);
				l.add(date);
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}
		sql.append(" and status='1'");
		sql.append(" order by update_time desc");
		return DbBuilder.getCount(sql.toString(),l.toArray());
	}

	/**
	 * 修改用户
	 * @param user
	 * @return
	 */
	public Integer updateUser(User user){
		StringBuffer sql =new StringBuffer();
		sql.append("update user set name=?,real_name=?,password=?,birthday=?,oper=?,update_time=? where id=?");
		List<Object> list=new ArrayList<Object>();
		list.add(user.getName());
		list.add(user.getRealName());
		list.add(user.getPassword());
		list.add(user.getBirthday());
		list.add(user.getOper());
		list.add(user.getUpdateTime());
		list.add(user.getId());
		return DbBuilder.update(sql.toString(), list.toArray());
	}
	/**
	 * 添加用户
	 * @param user
	 * @return
	 */
	public Integer addUser(User user){
		StringBuffer sql =new StringBuffer();
		sql.append("insert into user values(?,?,?,?,?,?,?,?,?,?,?)");
		List<Object> list=new ArrayList<Object>();
		list.add(user.getId());
		list.add(user.getName());
		list.add(user.getRealName());
		list.add(user.getPassword());
		list.add(user.getDeptId());
		list.add(user.getRoleId());
		list.add(user.getOper());
		list.add(user.getBirthday());
		list.add(user.getCreateTime());
		list.add(user.getUpdateTime());
		list.add(user.getStatus());
		return DbBuilder.save(sql.toString(), list.toArray());
	}
 }
